package org.hackystat.projecttrajectory.db.derby;

import static org.hackystat.projecttrajectory.server.ServerProperties.DB_DIR_KEY;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import javax.xml.datatype.XMLGregorianCalendar;
import org.hackystat.projecttrajectory.db.DbImplementation;
import org.hackystat.projecttrajectory.resource.estimate.jaxb.EstimateData;
import org.hackystat.projecttrajectory.resource.estimate.jaxb.ProjectEstimate;
import org.hackystat.projecttrajectory.server.Server;
import org.hackystat.sensorbase.resource.projects.jaxb.Project;
import org.hackystat.sensorbase.resource.users.jaxb.User;
import org.hackystat.utilities.stacktrace.StackTrace;
import org.hackystat.utilities.tstamp.Tstamp;

/**
 * Provides an implementation of {@link DbImplementation} using Derby in
 * embedded mode.
 * 
 * @author Cam Moore
 */
public class DerbyImplementation extends DbImplementation {

  /** The JDBC driver. */
  private static final String driver = "org.apache.derby.jdbc.EmbeddedDriver";

  /** The Database name. */
  private static final String dbName = "projecttrajectory";

  /** The Derby connection URL. */
  private static final String connectionURL = "jdbc:derby:" + dbName + ";create=true";

  /**
   * The key for putting/retrieving the directory where Derby will create its
   * databases.
   */
  private static final String derbySystemKey = "derby.system.home";

  /**
   * The SQL state indicating that INSERT tried to add data to a table with a
   * preexisting key.
   */
  private static final String DUPLICATE_KEY = "23505";

  /** The logger message for Derby errors. */
  private static final String derbyError = "Derby: Error ";

  /** The logger message for connection closing errors. */
  private static final String errorClosingMsg = "Derby: Error while closing. \n";
  /** The logger message when executing a query. */
  private static final String executeQueryMsg = "Derby: Executing query ";
  /** quoteAnd a String for PMD */
  private static final String quoteAnd = "' AND ";
  /** ownerQuote a String for PMD */
  private static final String ownerQuote = "Owner = '";
  /** projectNameQuote a String for PMD */
  private static final String projectNameQuote = "ProjectName ='";
  private static final String beginIndex = "<EstimateIndex>";
  private static final String endIndex = "</EstimateIndex>";
  private static final String estimateColumn = "XMLProjectEstimate";
  private static final String refColumn = "XmlProjectEstimateRef";
  private static final String selectProjectEstimate = 
    "SELECT XmlProjectEstimate FROM ProjectEstimate WHERE ";
  
  /** The SQL string for creating the ProjectEstimate table. */
  private static final String createProjectEstimateTableStatement = "create table ProjectEstimate  "
      + "("
      + " ProjectName VARCHAR(128) NOT NULL, "
      + " Owner VARCHAR(128) NOT NULL, "
      + " ProjectTstamp TIMESTAMP NOT NULL, "
      + " TotalSize INTEGER NOT NULL, "
      + " TotalEffort INTEGER NOT NULL, "
      + " ActiveDays INTEGER NOT NULL, "
      + " EstimateTime TIMESTAMP NOT NULL, "
      + " XmlProjectEstimate VARCHAR(32000) NOT NULL, "
      + " XmlProjectEstimateRef VARCHAR(1000) NOT NULL, "
      + " PRIMARY KEY (ProjectName, Owner, EstimateTime) " + ")";
  /**
   * An SQL string to test whether the ProjectEstimate table exists and has the
   * correct schema.
   */
  private static final String testProjectEstimateTableStatement = " UPDATE ProjectEstimate SET "
      + " ProjectName = 'TestProject', " + " Owner = 'TestUser', "
      + " ProjectTstamp = '"
      + new Timestamp(new Date().getTime()).toString()
      + "', " // NOPMD (dup string)
      + " TotalSize = 5432, " + " TotalEffort = 1025, " + " ActiveDays = 32, "
      + " EstimateTime = '" + new Timestamp(new Date().getTime()).toString()
      + "', XmlProjectEstimate = 'testXmlResource'," 
      + " XmlProjectEstimateRef = 'testXmlResourceRef'" + " WHERE 1=3"; // NOPMD
                                                                        // (duplicate
                                                                        // string)
  
  /** Indicates whether this database was initialized or was pre-existing. */
  private boolean isFreshlyCreated;

  /**
   * Instantiates the Derby implementation. Throws a Runtime exception if the
   * Derby jar file cannot be found on the classpath.
   * 
   * @param server The ProjectTrajectory Server.
   */
  public DerbyImplementation(Server server) {
    super(server);
    // Set the directory where the DB will be created and/or accessed.
    // This must happen before loading the driver.
    String dbDir = server.getServerProperties().get(DB_DIR_KEY);
    System.getProperties().put(derbySystemKey, dbDir);
    // Try to load the driver
    try {
      Class.forName(driver);
    } catch (java.lang.ClassNotFoundException e) {
      String msg = "Derby: Exception during DbManager initialization: Derby not on CLASSPATH.";
      this.logger.warning(msg + "\n" + StackTrace.toString(e));
      throw new RuntimeException(msg, e);
    }
  }

  /**
   * Initialize the database by creating tables for each resource type.
   * 
   * @throws SQLException If table creation fails.
   */
  private void createTables() throws SQLException {
    Connection conn = null;
    Statement s = null;
    try {
      conn = DriverManager.getConnection(connectionURL);
      s = conn.createStatement();
      s.execute(createProjectEstimateTableStatement);
      s.close();
    } finally {
      if (s != null) {
        s.close();
      }
      if (conn != null) {
        conn.close();
      }
    }
  }

  /** {@inheritDoc} */
  @Override
  public void deleteProjectEstimate(Project project, XMLGregorianCalendar timestamp) {
    String statement = "DELETE FROM ProjectEstimate WHERE " + projectNameQuote + project.getName()
        + quoteAnd + ownerQuote + project.getOwner() + quoteAnd + "ProjectTstamp = "
        + Tstamp.makeTimestamp(timestamp) + "'";
    deleteResource(statement);
  }

  /** {@inheritDoc} */
  @Override
  public void deleteProjectEstimate(String estimator, String projectName) {
    String statement = "DELETE FROM ProjectEstimate WHERE " + projectNameQuote + projectName
        + quoteAnd + ownerQuote + estimator + "'";
    deleteResource(statement);
  }


  /**
   * Deletes the resource, given the SQL statement to perform the delete.
   * 
   * @param statement The SQL delete statement.
   */
  private void deleteResource(String statement) {
    Connection conn = null;
    PreparedStatement s = null;
    try {
      conn = DriverManager.getConnection(connectionURL);
      server.getLogger().fine("Derby: " + statement);
      s = conn.prepareStatement(statement);
      s.executeUpdate();
    } catch (SQLException e) {
      this.logger.info("Derby: Error in deleteResource()" + StackTrace.toString(e));
    } finally {
      try {
        s.close();
        conn.close();
      } catch (SQLException e) {
        this.logger.warning(errorClosingMsg + StackTrace.toString(e));
      }
    }
  }
  /** {@inheritDoc} */
  @Override
  public String getProjectEstimateIndex(Project project) {
    String statement = selectProjectEstimate
      + projectNameQuote + project.getName() + "'";
    StringBuilder builder = new StringBuilder(512);
    builder.append(beginIndex);
    builder.append(executeColumnQuery(statement, estimateColumn));
    builder.append(endIndex);
    return builder.toString();
  }

  /** {@inheritDoc} */
  @Override
  public String getProjectEstimateIndex(String projectName) {
    String statement = "SELECT XmlProjectEstimateRef FROM ProjectEstimate WHERE "
      + projectNameQuote + projectName + "'";
    StringBuilder builder = new StringBuilder(512);
    builder.append(beginIndex);
    builder.append(executeColumnQuery(statement, refColumn));
    builder.append(endIndex);
    return builder.toString();
  }

  /** {@inheritDoc} */
  @Override
  public String getUserEstimateIndex(User user) {
    String statement =
      "SELECT XmlProjectEstimateRef FROM ProjectEstimate WHERE "
      + ownerQuote + user.getEmail() + "'";
    StringBuilder builder = new StringBuilder(512);
    builder.append(beginIndex);
    builder.append(executeColumnQuery(statement, refColumn));
    builder.append(endIndex);
    return builder.toString();
  }

  /** {@inheritDoc} */
  @Override
  public String getUserEstimateIndex(String user) {
    String statement =
      "SELECT XmlProjectEstimateRef FROM ProjectEstimate WHERE "
      + ownerQuote + user + "'";
    StringBuilder builder = new StringBuilder(512);
    builder.append(beginIndex);
    builder.append(executeColumnQuery(statement, refColumn));
    builder.append(endIndex);
    return builder.toString();
  }

  /** {@inheritDoc} */
  @Override
  public String getProjectEstimate(User user, Project project) {
    String statement = selectProjectEstimate
      + projectNameQuote + project.getName() + quoteAnd
      + ownerQuote + user.getEmail() + "'";
    return executeColumnQuery(statement, estimateColumn);
  }

  /** {@inheritDoc} */
  @Override
  public String getProjectEstimate(String user, String project) {
    String statement = selectProjectEstimate
      + projectNameQuote + project + quoteAnd
      + ownerQuote + user + "'";
    return executeColumnQuery(statement, estimateColumn);
  }
  
  /** {@inheritDoc} */
  @Override
  public String getProjectEstimateIndex() {
    String statement = "SELECT XmlProjectEstimateRef FROM ProjectEstimate";
    StringBuilder builder = new StringBuilder(512);
    builder.append(beginIndex);
    builder.append(executeColumnQuery(statement, refColumn));
    builder.append(endIndex);
    return builder.toString();
  }

  
  /** {@inheritDoc} */
  @Override
  public boolean hasProjectEstimate(Project project) {
    String statement = selectProjectEstimate
      + projectNameQuote + project.getName() + "'";
    String results = executeColumnQuery(statement, estimateColumn);
    System.out.println(results);
    return !"".equals(results);
  }

  /** {@inheritDoc} */
  @Override
  public boolean hasProjectEstimate(Project project, XMLGregorianCalendar timestamp) {
    String statement = selectProjectEstimate
    + projectNameQuote + project.getName() + quoteAnd
    + "EstimateTime='" + Tstamp.makeTimestamp(timestamp) + "'";
  String results = executeColumnQuery(statement, estimateColumn);
  System.out.println(results);
  return !"".equals(results);
  }

  /** {@inheritDoc} */
  @Override
  public void initialize() {
    try {
      // Create a shutdown hook that shuts down derby.
      Runtime.getRuntime().addShutdownHook(new Thread() {
        /** Run the shutdown hook for shutting down Derby. */
        @Override
        public void run() {
          Connection conn = null;
          try {
            conn = DriverManager.getConnection("jdbc:derby:;shutdown=true");
          } catch (Exception e) {
            System.out.println("Derby shutdown hook results: " + e.getMessage());
          } finally {
            try {
              if (conn != null) {
                conn.close();
              }
            } catch (Exception e) { // NOPMD
              // we tried.
            }
          }
        }
      });
      // Initialize the tables if necessary
      this.isFreshlyCreated = !isPreExisting();
      String dbStatusMsg = (this.isFreshlyCreated) ? "Derby: uninitialized."
          : "Derby: previously initialized.";
      this.logger.info(dbStatusMsg);
      if (this.isFreshlyCreated) {
        this.logger.info("Derby: creating DB in: " + System.getProperty(derbySystemKey));
        createTables();
      }
    } catch (Exception e) {
      String msg = "Derby: Exception during DerbyImplementation initialization:";
      this.logger.warning(msg + "\n" + StackTrace.toString(e));
      throw new RuntimeException(msg, e);
    }
  }

  /** {@inheritDoc} */
  @Override
  public boolean isFreshlyCreated() {
    return this.isFreshlyCreated;
  }

  /**
   * Determine if the database has already been initialized with correct table
   * definitions. Table schemas are checked by seeing if a dummy insert on the
   * table will work OK.
   * 
   * @return True if the database exists and tables are set up correctly.
   * @throws SQLException If problems occur accessing the database or the tables
   *           are set right.
   */
  private boolean isPreExisting() throws SQLException {
    Connection conn = null;
    Statement s = null;
    try {
      conn = DriverManager.getConnection(connectionURL);
      s = conn.createStatement();
      s.execute(testProjectEstimateTableStatement);
    } catch (SQLException e) {
      String theError = (e).getSQLState();
      if ("42X05".equals(theError)) {
        // Database doesn't exist.
        return false;
      } else if ("42X14".equals(theError) || "42821".equals(theError)) {
        // Incorrect table definition.
        throw e;
      } else {
        // Unknown SQLException
        throw e;
      }
    } finally {
      if (s != null) {
        s.close();
      }
      if (conn != null) {
        conn.close();
      }
    }
    // If table exists will get - WARNING 02000: No row was found
    return true;
  }

  /**
   * Creates the string for the EstimateRef.
   * @param estimate the ProjectEstimate.
   * @param data the EstimateData.
   * @return The XML String representing the EstimateRef.
   */
  private String makeEstimateRef(ProjectEstimate estimate, EstimateData data) {
    StringBuffer buff = new StringBuffer(128);
    buff.append("<EstimateRef Project=\"");
    buff.append(estimate.getProject());
    buff.append("\" Estimator=\"");
    buff.append(estimate.getEstimator());
    buff.append("\" href=\"");
    buff.append(this.server.getHostName());
    buff.append('/');
    buff.append(estimate.getEstimator());
    buff.append('/');
    buff.append(estimate.getProject());
    buff.append("\" LastMod=\"");
    buff.append(data.getEstimateDate());
    buff.append("\"/>");

    return buff.toString();
  }

  /** {@inheritDoc} */
  @Override
  public boolean storeProjectEstimate(ProjectEstimate estimate, String xmlProjectEstimate,
      String xmlEstimateRef) {
    Connection conn = null;
    PreparedStatement s = null;
    List<EstimateData> dataList = estimate.getEstimateData();
    try {
      conn = DriverManager.getConnection(connectionURL);
      for (EstimateData data : dataList) {
        try {
          s = conn.prepareStatement("INSERT INTO ProjectEstimate VALUES "
              + "(?, ?, ?, ?, ?, ?, ?, ?, ?)");
          // Order: ProjectName, Owner, ProjectTstamp, TotalSize, TotalEffort,
          // ActiveDays, EstimateTime, XMLProjectEstimate, XMLProjectEstimateRef
          s.setString(1, estimate.getProject());
          s.setString(2, estimate.getEstimator());
          XMLGregorianCalendar time = estimate.getLastModDate();
          s.setTimestamp(3, Tstamp.makeTimestamp(time));
          s.setInt(4, data.getTotalSize().intValue());
          s.setInt(5, data.getTotalEffort().intValue());
          s.setInt(6, data.getActiveDays().intValue());
          s.setTimestamp(7, Tstamp.makeTimestamp(data.getEstimateDate()));
          s.setString(8, xmlProjectEstimate);
          s.setString(9, makeEstimateRef(estimate, data));
          s.executeUpdate();
          this.logger.fine("Derby: Inserted estimate for " + estimate.getProject() + " Size: "
              + data.getTotalEffort() + " Effort: " + data.getTotalEffort());
        } catch (SQLException e) {
          if (DUPLICATE_KEY.equals(e.getSQLState())) {
            try {
              // try an update instead of insert
              s = conn.prepareStatement("UPDATE ProjectEstimate SET "
                  + "ProjectName=?, Owner=?, ProjectTstamp=?, TotalSize=?, TotalEffort=?, "
                  + "ActiveDays=?, EstimateTime=?, XmlProjectEstimate=?, XmlProjectEstimateRef=?" 
                  + " WHERE ProjectName=?"
                  + " AND EstimateTime=?");
              s.setString(1, estimate.getProject());
              s.setString(2, estimate.getEstimator());
              XMLGregorianCalendar time = estimate.getLastModDate();
              s.setTimestamp(3, Tstamp.makeTimestamp(time));
              s.setInt(4, data.getTotalSize().intValue());
              s.setInt(5, data.getTotalEffort().intValue());
              s.setInt(6, data.getActiveDays().intValue());
              s.setTimestamp(7, Tstamp.makeTimestamp(data.getEstimateDate()));
              s.setString(8, xmlProjectEstimate);
              s.setString(9, makeEstimateRef(estimate, data));
              s.setString(10, estimate.getProject());
              s.setTimestamp(11, Tstamp.makeTimestamp(data.getEstimateDate()));
              s.executeUpdate();
              this.logger.fine("Derby: Updated estimate for " + estimate.getProject() + " Size: "
                  + data.getTotalEffort() + " Effort: " + data.getTotalEffort());
            } catch (SQLException sqle) {
              this.logger.info(derbyError + StackTrace.toString(sqle));
            }
          }
        }
      }
    } catch (SQLException e) {
      // failed to create the connection
      System.err.println("failed to create the connection");
    } finally {
      if (s != null) {
        try {
          s.close();
        } catch (SQLException e) {
          this.logger.warning(derbyError + StackTrace.toString(e));
        }
      }
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {
          this.logger.warning(derbyError + StackTrace.toString(e));
        }
      }
    }
    return false;
  }
  
  
  /**
   * @param statement the SQL statement.
   * @param column the column in the results to append to the string.
   * @return The results of the query for the given column.
   */
  private String executeColumnQuery(String statement, String column) {
    StringBuilder builder = new StringBuilder(512);
    Connection conn = null;
    PreparedStatement s = null;
    ResultSet rs = null;
    try {
      conn = DriverManager.getConnection(connectionURL);
      server.getLogger().fine(executeQueryMsg + statement);
      s = conn.prepareStatement(statement);
      rs = s.executeQuery();
      while (rs.next()) { // the select statement must guarantee only one row is
        // returned.
        builder.append(rs.getString(column));
      }
    } catch (SQLException e) {
      this.logger.info("DB: Error in getResource()" + StackTrace.toString(e));
    } finally {
      try {
        rs.close();
        s.close();
        conn.close();
      } catch (SQLException e) {
        this.logger.warning(errorClosingMsg + StackTrace.toString(e));
      }
    }
    return builder.toString();    
  }
}
